package com.lagou.studentsystem.dao.Impl;

import com.lagou.studentsystem.dao.ClassesDao;
import com.lagou.studentsystem.model.Classes;
import com.lagou.studentsystem.model.PageBean;
import com.lagou.studentsystem.model.Student;
import com.lagou.studentsystem.util.DruidUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ClassesDaoImpl implements ClassesDao {

    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    @Override
    public List<Classes> classesShow(PageBean pageBean) {
        List<Classes> classesList = null;
        try {
            // 1.获取Connection连接对象
            connection = DruidUtils.getConnection();
            // 2.使用StringBuilder类型的对象来描述SQL语句的目的在于后续可以发生改变
            StringBuilder stringBuilder = new StringBuilder("select * from class");
            // 3.当输入分页的需求时进行分页查询，于是拼接SQL语句
            if (pageBean != null) {
                stringBuilder.append(" limit " + pageBean.getStart() + "," + pageBean.getRow());
            }
            // 4.获取PreparedStatement并执行上述SQL语句
            preparedStatement = connection.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            // 5.将结果集中的数据取出并放到List集合中返回
            classesList = new ArrayList<>();
            while (resultSet.next()) {
                Classes classes = new Classes();
                classes.setC_id(resultSet.getString("c_id"));
                classes.setC_name(resultSet.getString("c_name"));
                classes.setC_grade(resultSet.getInt("c_grade"));
                classes.setC_teacher(resultSet.getString("c_teacher"));
                classes.setC_slogan(resultSet.getString("c_slogan"));
                classes.setC_size(resultSet.getInt("c_size"));
                classesList.add(classes);
            }
            return classesList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtils.close(connection, preparedStatement, resultSet);
        }
        return null;
    }

    @Override
    public List<Classes> getClassesAll() {
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "select * from class";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            List<Classes> classesList = new ArrayList<>();
            while (resultSet.next()){
                Classes classes = new Classes();
                classes.setC_id(resultSet.getString("c_id"));
                classes.setC_name(resultSet.getString("c_name"));
                classes.setC_grade(resultSet.getInt("c_grade"));
                classes.setC_teacher(resultSet.getString("c_teacher"));
                classes.setC_slogan(resultSet.getString("c_slogan"));
                classes.setC_size(resultSet.getInt("c_size"));
                classesList.add(classes);
            }
            return classesList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement,resultSet);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public int insertClasses(Classes classes) {
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "insert into class values (?,?,?,?,?,?) ;";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,classes.getC_id());
            preparedStatement.setString(2,classes.getC_name());
            preparedStatement.setInt(3,classes.getC_grade());
            preparedStatement.setString(4,classes.getC_teacher());
            preparedStatement.setString(5,classes.getC_slogan());
            preparedStatement.setInt(6,classes.getC_size());
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return 0;

    }

    @Override
    public int deleteClassesById(String id) {

        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "delete from class where c_id = ? ;";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,id);
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public int updateClassesById(String id, Classes classes) {

        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "update class set c_id = ? ,c_name = ?,c_grade = ?,c_teacher = ?,c_slogan = ?,c_size = ? where c_id = ? ;";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,classes.getC_id());
            preparedStatement.setString(2,classes.getC_name());
            preparedStatement.setInt(3,classes.getC_grade());
            preparedStatement.setString(4,classes.getC_teacher());
            preparedStatement.setString(5, classes.getC_slogan());
            preparedStatement.setInt(6,classes.getC_size());
            preparedStatement.setString(7,id);
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return 0;

    }

    @Override
    public Classes getClassesById(String id) {
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "select * from class where c_id = ? ";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,id);
            resultSet = preparedStatement.executeQuery();
            Classes classes = new Classes();
            while (resultSet.next()){
                classes.setC_id(resultSet.getString("c_id"));
                classes.setC_name(resultSet.getString("c_name"));
                classes.setC_grade(resultSet.getInt("c_grade"));
                classes.setC_teacher(resultSet.getString("c_teacher"));
                classes.setC_slogan(resultSet.getString("c_slogan"));
                classes.setC_size(resultSet.getInt("c_size"));
            }
            return classes;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement,resultSet);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public List<Classes> getClassesByName(String name) {
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "select * from class where c_teacher = ? ";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            resultSet = preparedStatement.executeQuery();
            List<Classes> classesList = new ArrayList<>();
            Classes classes = new Classes();
            while (resultSet.next()){
                classes.setC_id(resultSet.getString("c_id"));
                classes.setC_name(resultSet.getString("c_name"));
                classes.setC_grade(resultSet.getInt("c_grade"));
                classes.setC_teacher(resultSet.getString("c_teacher"));
                classes.setC_slogan(resultSet.getString("c_slogan"));
                classes.setC_size(resultSet.getInt("c_size"));
                classesList.add(classes);
            }
            return classesList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement,resultSet);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}
